package com.mbc.hr.manager;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

import javax.persistence.NoResultException;

import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.util.JRLoader;

import com.mbc.common.entities.Branch;
import com.mbc.common.exception.DataNotFoundException;
import com.mbc.common.manager.AbstractMgr;
import com.mbc.hr.entities.Department;
import com.mbc.hr.entities.Employee;
import com.mbc.hr.entities.JobTitle;
import com.mbc.hr.param.EarlinessNLatenessCriteria;

public class AttendanceMgmtReportMgr extends AbstractMgr {
	public AttendanceMgmtReportMgr(){}
	
	public AttendanceMgmtReportMgr(String sessionId){
		this.sessionId = sessionId;
	}
	
	public JasperPrint prepareLatenessReportByEmployeeNo(EarlinessNLatenessCriteria cri, String rptFilePath)throws Exception{
		String l_employeeName = "";
		String l_branchName = "";
		String l_title = "";
		String l_departmentName = "";
		openConnection();
		try {
			try {
				Employee l_emp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
						.setParameter("employeeNo", cri.getEmployeeNo())
						.getSingleResult();
				l_employeeName = l_emp.getName();
				l_branchName = entityManager.find(Branch.class, l_emp.getBranchId()).getName();
				l_title = entityManager.find(JobTitle.class, l_emp.getJobTitleID()).getTitle();
				if(l_emp.getDepartmentID() != null)
					l_departmentName = entityManager.find(Department.class, l_emp.getDepartmentID()).getDepartmentName();
			} catch (NoResultException e) {
				throw new DataNotFoundException("Employee No. : " + cri.getEmployeeNo() + " does not exist.");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
		
		openJDBCConnection();
		JasperPrint l_jsPrint = null;
		try {
			
			PreparedStatement l_pstmt = getConnection().prepareStatement("call sp_rpt_LatenessAndEarlyOutByEmployeeNo(?,?,?,?)");
			int i = 1;
			l_pstmt.setString(i++, cri.getEmployeeNo());
			l_pstmt.setDate(i++, new Date(cri.getFromDate().getTime()));
			l_pstmt.setDate(i++, new Date(cri.getToDate().getTime()));
			l_pstmt.setByte(i++, cri.getType());
			ResultSet l_rs = l_pstmt.executeQuery();
			JRResultSetDataSource l_jrRS = new JRResultSetDataSource(l_rs);
			JasperReport l_jsRpt =(JasperReport)JRLoader.loadObjectFromFile(rptFilePath);
			Map<String, Object> l_params = new HashMap<String, Object>();
			l_params.put("Name", l_employeeName);
			l_params.put("EmployeeNo", cri.getEmployeeNo());
			l_params.put("Branch", l_branchName);
			l_params.put("Department", l_departmentName);
			l_params.put("JobTitle", l_title);
			l_params.put("FromDate", cri.getFromDate());
			l_params.put("ToDate", cri.getToDate());
			l_params.put("Type", cri.getType());
			l_jsPrint = JasperFillManager.fillReport(l_jsRpt, l_params, l_jrRS);
		} catch (Exception e) {
			throw e;
		} finally {
			closeJDBCConnection();
		}

		return l_jsPrint;
	}
	
	public JasperPrint prepareLatenessSummaryReport(EarlinessNLatenessCriteria cri, String rptFilePath)throws Exception{
		openJDBCConnection();
		JasperPrint l_jsPrint = null;
		try {
			PreparedStatement l_pstmt = getConnection().prepareStatement("call sp_rpt_LatenessAndEarlyOut(?,?,?,?,?)");
			int i = 1;
			l_pstmt.setLong(i++, cri.getDepartmentID());
			l_pstmt.setLong(i++, cri.getBranchId());
			l_pstmt.setDate(i++, new Date(cri.getFromDate().getTime()));
			l_pstmt.setDate(i++, new Date(cri.getToDate().getTime()));
			l_pstmt.setByte(i++, cri.getType());
			ResultSet l_rs = l_pstmt.executeQuery();
			JRResultSetDataSource l_jrRS = new JRResultSetDataSource(l_rs);
			JasperReport l_jsRpt =(JasperReport)JRLoader.loadObjectFromFile(rptFilePath);
			Map<String, Object> l_params = new HashMap<String, Object>();
			l_params.put("Branch", cri.getBranchId() == 0 ? "" : cri.getBranchName());
			l_params.put("Department", cri.getDepartmentID() == 0 ? "" : cri.getDepartmentName());
			l_params.put("FromDate", cri.getFromDate());
			l_params.put("ToDate", cri.getToDate());
			l_params.put("Type", cri.getType());
			l_jsPrint = JasperFillManager.fillReport(l_jsRpt, l_params, l_jrRS);
		} catch (Exception e) {
			throw e;
		} finally {
			closeJDBCConnection();
		}

		return l_jsPrint;
	}

	public JasperPrint prepareDailyAttendance(java.util.Date attendanceDate, long branchID, String branchName, String rptFilePath)throws Exception{
		openJDBCConnection();
		JasperPrint l_jsPrint = null;
		try {
			PreparedStatement l_pstmt = getConnection().prepareStatement("call sp_rpt_dailyattendance(?,?)");
			int i = 1;
			l_pstmt.setLong(i++, branchID);
			l_pstmt.setDate(i++, new Date(attendanceDate.getTime()));
			ResultSet l_rs = l_pstmt.executeQuery();
			JRResultSetDataSource l_jrRS = new JRResultSetDataSource(l_rs);
			JasperReport l_jsRpt =(JasperReport)JRLoader.loadObjectFromFile(rptFilePath);
			Map<String, Object> l_params = new HashMap<String, Object>();
			l_params.put("AttendanceDate", attendanceDate);
			l_params.put("Branch", branchName);
			l_jsPrint = JasperFillManager.fillReport(l_jsRpt, l_params, l_jrRS);
		} catch (Exception e) {
			throw e;
		} finally {
			closeJDBCConnection();
		}
		return l_jsPrint;
	}
	
	public JasperPrint prepareAbsence(java.util.Date fromDate, java.util.Date toDate, String employeeNo, String rptFilePath)throws Exception{
		String l_branchName = "";
		String l_departmentName = "";
		String l_title = "";
		Employee l_emp = null;
		openConnection();
		try {
			try {
				l_emp = entityManager.createNamedQuery("Employee.findByEmployeeNo", Employee.class)
						.setParameter("employeeNo", employeeNo)
						.getSingleResult();
				l_branchName = entityManager.find(Branch.class, l_emp.getBranchId()).getName();
				l_title = entityManager.find(JobTitle.class, l_emp.getJobTitleID()).getTitle();
				if(l_emp.getDepartmentID() != null)
					l_departmentName = entityManager.find(Department.class, l_emp.getDepartmentID()).getDepartmentName();
			} catch (NoResultException e) {
				throw new DataNotFoundException("Employee No : " + employeeNo + " is not found.");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
		
		JasperPrint l_jsPrint = null;
		openJDBCConnection();
		try {
			PreparedStatement l_pstmt = getConnection().prepareStatement("call sp_rpt_Absence(?,?,?)");
			int i = 1;
			l_pstmt.setLong(i++, l_emp.getEmployeeID());
			l_pstmt.setDate(i++, new Date(fromDate.getTime()));
			l_pstmt.setDate(i++, new Date(toDate.getTime()));
			ResultSet l_rs = l_pstmt.executeQuery();
			JRResultSetDataSource l_jrRS = new JRResultSetDataSource(l_rs);
			JasperReport l_jsRpt =(JasperReport)JRLoader.loadObjectFromFile(rptFilePath);
			Map<String, Object> l_params = new HashMap<String, Object>();
			l_params.put("Branch", l_branchName);
			l_params.put("Department", l_departmentName);
			l_params.put("Name", l_emp.getName());
			l_params.put("EmployeeNo", l_emp.getEmployeeNo());
			l_params.put("JobTitle", l_title);
			l_params.put("FromDate", fromDate);
			l_params.put("ToDate", toDate);
			l_jsPrint = JasperFillManager.fillReport(l_jsRpt, l_params, l_jrRS);
		} catch (Exception e) {
			throw e;
		} finally {
			closeJDBCConnection();
		}
		return l_jsPrint;
	}
	
	public JasperPrint prepareAbsenceSummary(long departmentID, long branchID, java.util.Date fromDate, java.util.Date toDate, String rptFilePath)throws Exception{
		String l_branchName = "";
		String l_departmentName = "";
		openConnection();
		try {
			if(branchID != 0)
				l_branchName = entityManager.find(Branch.class, branchID).getName();
			if(departmentID != 0)
				l_departmentName = entityManager.find(Branch.class, departmentID).getName();
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
		
		openJDBCConnection();
		JasperPrint l_jsPrint = null;
		try {
			PreparedStatement l_pstmt = getConnection().prepareStatement("call sp_rpt_AbsenceSummary(?,?,?,?)");
			int i = 1;
			l_pstmt.setLong(i++, departmentID);
			l_pstmt.setLong(i++, branchID);
			l_pstmt.setDate(i++, new Date(fromDate.getTime()));
			l_pstmt.setDate(i++, new Date(toDate.getTime()));
			ResultSet l_rs = l_pstmt.executeQuery();
			JRResultSetDataSource l_jrRS = new JRResultSetDataSource(l_rs);
			JasperReport l_jsRpt =(JasperReport)JRLoader.loadObjectFromFile(rptFilePath);
			Map<String, Object> l_params = new HashMap<String, Object>();
			l_params.put("Branch", l_branchName);
			l_params.put("Department", l_departmentName);
			l_params.put("FromDate", fromDate);
			l_params.put("ToDate", toDate);
			l_jsPrint = JasperFillManager.fillReport(l_jsRpt, l_params, l_jrRS);
		} catch (Exception e) {
			throw e;
		} finally {
			closeJDBCConnection();
		}
		return l_jsPrint;
	}
}
